# 导入 did
# 2022-03-08
# yeyi

import openpyxl
import pymysql
import os

# 建立mysql链接
# 预生产
# db = pymysql.connect(host='10.123.2.10',
#                      port=3306,
#                      user='sirun',
#                      passwd='sirun@2019',
#                      db='bss-gq',
#                      charset='utf8')
# 联调
db = pymysql.connect(host='10.2.3.5',
                     port=3306,
                     user='root',
                     passwd='sirun@2018',
                     db='bss-gq',
                     charset='utf8')
cursor = db.cursor()  # 使用cursor()方法获取操作游标
# 打开excel文件,获取工作簿对象
wb = openpyxl.load_workbook(os.getcwd() +
                            "\\能力集脚本\\excel\\A58ECU.xlsx")
ws = wb.active  # 当前活跃的表单

vehicle_ser = "58"
# 联调
# vehicle_model_ids = {30, 31, 33, 34, 35}
# 预生产
vehicle_model_ids = {58000}
number = 1
for vehicle_model_id in vehicle_model_ids:
    idStr = str(vehicle_model_id)
    for i in range(2, 256):
        item = {}
        item['A58'] = ws.cell(row=i, column=8).value
        if item['A58'] is None or item['A58'] in '×':
            continue

        item['ecu_id'] = vehicle_ser+idStr+"%03d" % number
        item['vehicle_model_id'] = idStr
        item['ecu_code'] = ws.cell(row=i, column=1).value
        item['ecu_name'] = ws.cell(row=i, column=4).value.replace(" ", "")
        item['ecu_full_name'] = ws.cell(row=i, column=5).value
        item['ecu_describe'] = ws.cell(row=i, column=6).value
        try:
            tt = ws.cell(row=i, column=2).value
            tt2 = ws.cell(row=i, column=3).value
            item['ecu_diag_request_id'] = int(tt, 16)
            item['ecu_diag_response_id'] = int(tt2, 16)
        except Exception as e:
            print('change err: tt: '+tt+' tt2: '+' err: '+tt2+str(e))

        channel = ws.cell(row=i, column=7).value
        if channel == "ACAN" :
            item['ecu_diagnosis_channel'] = 1
        elif channel == "BCAN" :
            item['ecu_diagnosis_channel'] = 2
        elif channel == "ECAN" :
            item['ecu_diagnosis_channel'] = 3
        elif channel == "PCAN" :
            item['ecu_diagnosis_channel'] = 4
        elif channel == "TCAN" :
            item['ecu_diagnosis_channel'] = 5
        else:
            item['ecu_diagnosis_channel'] = 0
        number = number + 1
        print(item)
        into = "insert into `tab_ecu` (`ecu_id`, `vehicle_model_id`, `ecu_code`, `ecu_name`, `ecu_full_name`,`ecu_describe`," \
               "`ecu_diag_request_id`,`ecu_diag_response_id`,`ecu_diagnosis_channel`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        values = (item['ecu_id'], item['vehicle_model_id'], item['ecu_code'],
                  item['ecu_name'], item['ecu_full_name'], item['ecu_describe'],
                  item['ecu_diag_request_id'], item['ecu_diag_response_id'], item['ecu_diagnosis_channel'])
        try:
            cursor.execute(into, values)
            db.commit()
        except Exception as e:
            print('err: '+str(e))
